CREATE DATABASE East2West
go
USE East2West

go
CREATE TABLE Customer(
	CustomerID int identity(1,1) primary key,
	UserName nvarchar(50) not null,
	Password nvarchar(50) not null,
	CustomerName nvarchar(200) not null,
	Email nvarchar(200) not null,
	PhoneNumber nvarchar(50) not null,
	Address nvarchar(max) not null
)

go
CREATE TABLE Admin(
	AdminID int identity(1,1) primary key,
	AdminName nvarchar(200) not null,
	UserName nvarchar(50) not null,
	Password nvarchar(50) not null
)

go
CREATE TABLE PackageType(
	PackageTypeID int identity(1,1) primary key,
	PackageName nvarchar(200) not null,
	Description nvarchar(max) not null
)

go
CREATE TABLE Locations(
	LocationID int identity(1,1) primary key,
	LocationName nvarchar(200) not null,
	Description nvarchar(max) not null
)

go
CREATE TABLE Places(
	PlaceID int identity(1,1) primary key,
	LocationID int references Locations(LocationID),
	PlaceName nvarchar(200) not null,
	Description nvarchar(max) not null
)

go
CREATE TABLE Hotels(
	HotelID int identity(1,1) primary key,
	PlaceID int references Places(PlaceID),
	HotelName nvarchar(200) not null,
	HotelRate int not null,
	HotelPrice money not null -- per day 
)

go
CREATE TABLE PackageTour(
	PackageTourID int identity(1,1) primary key,
	PackageTypeID int references PackageType(PackageTypeID),
	HotelID int references Hotels(HotelID),
	LocationID int references Locations(LocationID),
	PlaceID int references Places(PlaceID),
	Price money not null,
	Travel nvarchar(100) not null,
	Description nvarchar(max) not null,
	Images nvarchar(200),
	FromDate datetime ,
	ToDate datetime ,
	Available bit default(1) --1:active / 0:waiting
)

go
CREATE TABLE TourBooking(
	TourBookingID int identity(1,1) primary key,
	CustomerID int references Customer(CustomerID),
	PackageTourID int references PackageTour(PackageTourID),
	Quantity int not null,
	TotalPrice money, --Price Package Tour * Quantity
	Available bit default(1) --1:done / 0:cancel
)

go
CREATE TABLE CarModel(
	ModelID int identity(1,1) primary key,
	ModelName nvarchar(200) not null,
	Description nvarchar(max) not null
)

go
CREATE TABLE CarDetails(
	CarID int identity(1,1) primary key,
	ModelID int references CarModel(ModelID),
	CarName nvarchar(200) not null,
	Capacity int not null,
	Description nvarchar(max) not null,
	Price money not null, --per day
	Images nvarchar(200)
)

go
CREATE TABLE CarBooking(
	CarBookingID int identity(1,1) primary key,
	CarID int references CarDetails(CarID),
	CustomerID int references Customer(CustomerID),
	Driver bit default(0), --1: required / 0: not required
	PickupDate datetime not null,
	DropoffDate datetime not null,
	Quantity int not null,
	TotalPrice money, --Price Car Details * Quantity
	Available bit default(1) --1: done / 0: cancel
)

go
CREATE TABLE Flight(
	FlightID int identity(1,1) primary key,
	FlightName nvarchar(200) not null,
	DepartTime datetime not null,
	ArrivalTime datetime not null,
	DepartLocation int references Locations(LocationID),
	ArrivalLocation int references Locations(LocationID)
)

go
CREATE TABLE Banking(
	CustomerID int references Customer(CustomerID),
	CreditCardNumber nvarchar(50) not null,
	PINCode nvarchar(50) not null,
	Balance money not null
)

go
CREATE TABLE FeedBack(
	FeedbackID int identity(1,1) primary key,
	CustomerID int references Customer(CustomerID),
	Title nvarchar(50),
	TextContent nvarchar(max) not null
)